﻿/****** Object:  StoredProcedure [dbo].[sp_JobSchedule_Select]    Script Date: 21/08/2024 12:19:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_JobSchedule_Select]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_JobSchedule_Select] AS' 
END
GO


ALTER PROCEDURE [dbo].[sp_JobSchedule_Select]
  @ImportName       VARCHAR(50),
  @PollDetails      VARCHAR(250) = NULL
AS
-- =============================================
-- Author:		Andrew Breward
-- Create date: AndyB 12/08/2023 v1.0.0
-- Descripton:	SP to retrieve all (enabled) Job Schedules. 
--              if PollDetails is passed in, this also updates the ImportPoll table to keep a record of when polling occured.
-- Modified:    AndyB 9/Jul/2024  Added ReRunNextPoll to SELECT statement.
-- Modified:    AndyB 21/Aug/2024 Added JobDescription to SELECT statement.
-- Example:     exec sp_JobSchedule_Select 'OneGradeImportService', 'Polling @ 12/08/2023 11:34:40 AM (version=0.0.1.11083)'
--              exec sp_JobSchedule_Select 'OneGradeImportService'
-- =============================================

--DECLARE @ImportServicePollFrequency      VARCHAR(100);
--DECLARE @ImportServiceEmailJobExecution  VARCHAR(100);

--decide whether to update the ImportPoll
IF (@PollDetails IS NOT NULL) 
BEGIN
	IF NOT EXISTS (SELECT 1 FROM JobPoll WHERE JobPoll.Name = @ImportName)
	BEGIN
		INSERT INTO JobPoll ([Name], PollDetails) VALUES (@ImportName, @PollDetails)	
	END
	ELSE
	BEGIN
		UPDATE JobPoll SET PollDetails = @PollDetails WHERE JobPoll.Name = @ImportName
	END
END

--these are to save making another round trip to the database for each poll
--SET @ImportServicePollFrequency     = (SELECT SystemSettingValue FROM SystemSettingValue WHERE SystemSettingName = 'ImportServicePollFrequency');
--SET @ImportServiceEmailJobExecution = (SELECT SystemSettingValue FROM SystemSettingValue WHERE SystemSettingName = 'ImportServiceEmailJobExecution');

SELECT 
	JobSchedule.ID                 AS JobScheduleID,
	JobSchedule.JobID              AS JobID,
	Job.JobName                    AS JobName, 
	Job.JobType                    AS JobType,
	Job.JobDescription             AS JobDescription,
	Job.JobTypeRefID               AS JobTypeRefID,
	JobSchedule.StartExecution     AS StartExecution,
	JobSchedule.StopExecution      AS StopExecution,
	JobSchedule.LastExecuted       AS LastExecuted,
	JobSchedule.ExecutionMessage   AS ExecutionMessage,
	IsNull(JobSchedule.ReRunNextPoll, 0)      AS ReRunNextPoll,
	IsNull(JobSchedule.ExecuteSunday, 0)      AS ExecuteSunday,
	IsNull(JobSchedule.ExecuteMonday, 0)      AS ExecuteMonday,
	IsNull(JobSchedule.ExecuteTuesday, 0)     AS ExecuteTuesday,     
	IsNull(JobSchedule.ExecuteWednesday, 0)   AS ExecuteWednesday,
	IsNull(JobSchedule.ExecuteThursday, 0)    AS ExecuteThursday,
	IsNull(JobSchedule.ExecuteFriday, 0)      AS ExecuteFriday,
	IsNull(JobSchedule.ExecuteSaturday, 0)    AS ExecuteSaturday,
	JobSchedule.Enabled             AS Enabled
FROM 
	JobSchedule
		INNER JOIN Job ON Job.ID = JobSchedule.JobID
WHERE
	Job.Enabled = 1
AND
	JobSchedule.Enabled = 1
ORDER BY
	1
GO





/****** Object:  StoredProcedure [dbo].[sp_JobSchedule_LastExecuted_Update]    Script Date: 29/07/2024 13:22:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_JobSchedule_LastExecuted_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_JobSchedule_LastExecuted_Update] AS' 
END
GO

ALTER PROCEDURE [dbo].[sp_JobSchedule_LastExecuted_Update]
@JobScheduleID       INTEGER,
@LastExecuted        DATETIME,
@ExecutionMessage    VARCHAR(max) = ''

AS
-- =============================================
-- Author:		Andrew Breward
-- Create date: AndyB 17/8/2023 v1.0.0
-- Descripton:	SP to Update the LastExecuted and ExecutionMessage fields in the JobSchedule table
-- Modified:    AndyB 9/Jul/2024 Added ReRunNextPoll to UPDATE statement.
--              AndyB 29/Jul/2024 Added SELECT @@ROWCOUNT
-- Example:     exec sp_JobSchedule_LastExecuted_Update 1, '2023-08-11 16:00:00.000', ''
-- =============================================

UPDATE 
  JobSchedule
SET    
  LastExecuted     = @LastExecuted,
  ExecutionMessage = @ExecutionMessage,
  ReRunNextPoll    = 0
WHERE
  ID = @JobScheduleID

  --return number of rows affected
SELECT @@ROWCOUNT
GO






/****** Object:  StoredProcedure [dbo].[sp_JobSchedule_SystemSettings_Select]    Script Date: 18/07/2024 09:27:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_JobSchedule_SystemSettings_Select]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_JobSchedule_SystemSettings_Select] AS' 
END
GO



ALTER PROCEDURE [dbo].[sp_JobSchedule_SystemSettings_Select]
AS
-- =============================================
-- Author:		Andrew Breward
-- Create date: AndyB 17/7/2024 
-- Descripton:	SP to retrieve the Settings associated with the Jobs (used by the ImportService)
-- Modified:    AndyB 07/Sep/2024 Added ImportServiceSendTestEmail
-- Modified:    AndyB 09/Sep/2024 Re-wrote in order to include DefaultValue
-- Example:     exec sp_JobSchedule_SystemSettings_Select
-- =============================================

SELECT 
	SystemSettingName              AS SystemSettingName,
	ISNull(DefaultValue, '')       AS SystemSettingDefaultValue, 
	ISNull(SystemSettingValue, '') AS SystemSettingValue
FROM
    SystemSettingValue
WHERE
    SystemSettingName LIKE 'ImportService%'
OR
    SystemSettingName LIKE 'Email%'
ORDER BY
    1
--
GO


